Standalone execution of incomplete data flows

ABSTRACT

Systems, devices, and methods for modularizing data flows and for providing stand-alone execution of incomplete data flows are provided based on an improved data warehousing framework. Data flow modularization and code reuse is improved through the use of the disclosed flowlets and allows for consistent and efficient standalone testing of incomplete data flows. The disclosed details enable various refinements and modifications according to system design considerations.

CROSS REFERENCE TO RELATED APPLICATIONS

This application claims priority to U.S. patent application Ser. No.60/913,807, filed on Apr. 24, 2007, entitled “SYSTEMS AND METHODS OFMODULARIZING DATA FLOWS”, the entirety of which is incorporated hereinby reference.

TECHNICAL FIELD

The subject disclosure relates to data warehousing tools and morespecifically to modularization and execution of reusable data flowcomponents.

BACKGROUND

Data warehousing databases were developed to meet a growing demand formanagement information and analysis that could not be met by operationaldatabase systems. While operational systems are optimized for simplicityand speed of modification (e.g., online transaction processing) throughheavy use of database normalization and an entity-relationship model,the data warehouse is optimized for reporting and analysis (e.g., onlineanalytical processing). As a result, operational systems were unable tomeet the need for management information for such reasons as theincreased processing load of reporting, which negatively impacted theresponse time of the operational systems. Moreover, development ofreports in operational systems often required writing specific computerprograms, which was slow and expensive.

The critical factor leading to the use of a data warehouse is desire fora data analyst to be able to perform complex queries and analyses (e.g.,data mining) on the information without slowing down the operationalsystems. There are many advantages to using a data warehouse such as theenhanced end-user access to a wide variety of data and increased abilityof business decision makers to obtain various kinds of trend reports(e.g., the item with the most sales in a geographical area for the lasttwo years). Advantageously, a data warehouse can be a significantenabler of commercial business applications, most notably customerrelationship management applications.

A critical process in any data warehousing scenario is the DataExtraction, Transformation, and Load (ETL) Process. ETL involves suchtasks as extracting data from outside sources, transforming it to fitbusiness needs, and ultimately loading it into the data warehouse. Whilean ETL process can be created using almost any programming language,creating them from scratch is quite complex, requiring high levels oftechnical expertise, with little opportunity to reuse existing codebases. Increasingly, companies have been seeking ETL tools to help inthe creation of ETL processes. One example of such a tool is MICROSOFT'sSQL Server Integration Services (SSIS), which provides a platform tobuild data integration and workflow applications.

In SSIS, the core ETL functions are performed within ‘Data Flow Tasks’.A Data Flow Task is an SSIS executable element responsible fortransferring and transforming data between heterogeneous data sources.It is the most important among SSIS executable elements and it has itsown complex logical structure with a specialized object model. A DataFlow consists of components and paths that connect the two. Together,paths and components build the data flow execution graph that controlsthe flow of data. The state of this graph is controlled by a modulecalled data flow (or Pipeline) layout.

Accordingly, data flows in SSIS are built using components that definethe sources that data comes from, the destinations that the data getsloaded to, and the transformations applied to data during the transfer.To be used in a dataflow, these source, destination, and transformationcomponents have to be configured by defining the associated metadata. Acomplete data flow describes the flow of data by specifying one or more‘source’ points that start the flow, operations downstream of the sourcepoints that operate on the data or direct the flow (e.g., merging orsplitting the flow), and one or more destination points that ‘sink’ thedata at the ends of the flow.

However, like previous data warehousing platforms, the existing SSISdata flow architecture does not offer the possibility of reusing partsof previously configured data flow components except through a crudecopy and paste functionality. For example, the copy and pastefunctionality alone is not sufficient to address the reusabilityproblem, because it does not offer any capability to manage pieces ofreusable logic. Furthermore, even with the ability to copy and pasteportions of reusable logic, additional remapping of metadata and manualfix-ups are still required, which does not mitigate the expense andrequired level of expertise to rapidly develop new data flows.Additionally, complex data flows could easily consist of hundreds ofcomponents. Currently, those components have to be placed within asingle data flow and there is no way to group or modularize them.

Another problem with existing architectures is the inability to addressdata flow scenarios where only partial portions of the flow are known,such as in the case of a reusable logic portion for example. Suchreusable logic portions can have one or more source or destinationpoints that are unknown or are unavailable, can have one or moreoperations within the flow that are unknown, or any combination of theseconditions. Such incomplete data flows cannot be executed on their own.For example, there is no convenient and consistent way to providestandalone execution of such incomplete data flows without significanteffort to provide test input sources and output destinations or providemanual fix-ups.

Accordingly, in consideration of the complexity, costs, and high levelof expertise required to reuse portions of ETL logic, it would bedesirable to provide a convenient way to generate, manage, and reusemodular portions of dataflow logic. It is further desirable to provide away for standalone execution such incomplete data flows. These and otherdeficiencies in the state of the art of data warehousing tools willbecome apparent from the description of the various exemplarynon-limiting embodiments of the invention set forth below.

SUMMARY

The following presents a simplified summary of the claimed subjectmatter in order to provide a basic understanding of some aspects of theclaimed subject matter. This summary is not an extensive overview of theclaimed subject matter. It is intended to neither identify key orcritical elements of the claimed subject matter nor delineate the scopeof the claimed subject matter. Its sole purpose is to present someconcepts of the claimed subject matter in a simplified form as a preludeto the more detailed description that is presented later.

In consideration of the foregoing, the invention provides an approach togenerate and execute modular reusable data flow modules (hereinafterknown as flowlets). In accordance with the embodiments described herein,flowlets can have one or more source or destination points that areunknown or are unavailable, can have one or more operations within theflow that are unknown, or they can have any combination of theseconditions. Flowlets can address the above problems and can allow aniterative approach in building SSIS data flows, by allowing pieces ofthe data flow logic to be built and tested separately through astand-alone execution process.

Furthermore, flowlets can consist of single or many data flow componentsconfigured to process data sets defined by its published metadata. Thesecomponents can form a common logic that can be used and reused in manydifferent data flows. The modular data flow design paradigm enabled byflowlets can further help standardize processes around designing anddeploying ETL logic, allow central storage of flowlet libraries, andprovides ease of maintenance. Furthermore, flowlets can be managed,deployed, executed, and tested with great flexibility and modularity inaccordance with the disclosed embodiments to allow efficient andconvenient reuse of portions of data flow logic.

According to one aspect of the invention, the SSIS data flow objectmodel is modified and extended to support the use of flowlets.

According to a further aspect of the invention, a new type of a dataflow designer is provided to present flowlet diagrams, create and editflowlet definitions, and view associated flowlet information.

According to a further aspect of the invention, a new package type tohold the flowlet definitions is provided.

According to a further aspect of the invention, the new features enabledby the use of flowlet modules in the SSIS data flows can be blended inwith the current data flow layout architecture to preserve existingobjects and interfaces, while implementing new functionality by addingnew flowlet components. Accordingly, new components (e.g., FlowletSource, Flowlet Destination, Flowlet Reference and Flowlet MetadataMapping) can be used to support flowlet-specific features. Additionally,new interfaces can expose new behavior of flowlet components todesigners (e.g., a flowlet reference component can implement anadditional interface to support connecting to flowlet definitions,caching, enumerating and refreshing the flowlet internals).

According to a further aspect of the invention, enhanced debugging andlogging capability is provided to support the use of flowlets in dataflows.

The following description and the annexed drawings set forth in detailcertain illustrative aspects of the claimed subject matter. Theseaspects are indicative, however, of but a few of the various ways inwhich the principles of the claimed subject matter may be employed andthe claimed subject matter is intended to include all such aspects andtheir equivalents. Other advantages and distinguishing features of theclaimed subject matter will become apparent from the following detaileddescription of the claimed subject matter when considered in conjunctionwith the drawings.

BRIEF DESCRIPTION OF THE DRAWINGS

The systems and methods for modularizing data flows and standaloneexecution thereof are further described with reference to theaccompanying drawings in which:

FIG. 1A illustrates an example of a complete data flow;

FIG. 1B illustrates an example of an incomplete data flow if one sourcepoint 102, one destination point 106_1 and one operation 104_3 from thecomplete data flow in FIG. 1A were not available;

FIG. 2 illustrates an example of 2 flowlets 200_1 and 200_2 formed fromthe incomplete data flow in FIG. 1B using flowlet source 202_ anddestination 206_ components;

FIG. 3 illustrates an example of data flow execution of an SSIS packageas provided without the use of flowlets;

FIG. 4A illustrates an exemplary execution flow of a new SSIS packagespecialized to hold flowlet definitions according to one aspect of thepresent invention;

FIG. 4B illustrates an exemplary block diagram of a system formodularizing data flows according to one aspect of the presentinvention;

FIG. 4C illustrates an exemplary block diagram of a process formodularizing data flows according to one aspect of the presentinvention;

FIG. 4D illustrates an exemplary block diagram of a process forstand-alone execution of incomplete data flows according to one aspectof the present invention;

FIG. 5A illustrates an exemplary data flow designer procedure ofselecting and extracting existing components in an existing data flow;

FIG. 5B illustrates an exemplary non-limiting block diagram of a processto modularize a data flow from a portion of an existing SSIS data flow;

FIG. 6A illustrates an exemplary simple data flow and the layout ofcolumns it uses;

FIG. 6B illustrates an exemplary flowlet definition diagram created byextracting components Transform 1 (604) and Transform 2 (606) of FIG.6A;

FIG. 6C illustrates an exemplary Flowlet Reference component 605 formedby the extracted components of FIG. 6A according to the process of FIG.5B;

FIG. 6D is an exemplary non-limiting block diagram illustrating thatinternal flowlet logic can be cached in the case of an Flowlet Referencecomponent formed by extracted components according to one aspect of theinvention;

FIG. 7A illustrates an exemplary SSIS Data Flow graph and executionpaths that can be produced according to one aspect of the invention;

FIG. 7B illustrates how exemplary execution paths can be built for theSSIS Data Flow graph of FIG. 7A after the desired flowlet logic isextracted;

FIGS. 7C and 7D illustrates an example of how columns (and buffers) canbe generated, referenced, and mapped when switching to and from theflowlet boundaries of FIGS. 7A and 7B;

FIG. 8 is a block diagram representing an exemplary non-limitingnetworked environment in which the present invention may be implemented;and

FIG. 9 is a block diagram representing an exemplary non-limitingcomputing system or operating environment in which the present inventionmay be implemented.

DETAILED DESCRIPTION Overview

As discussed in the background, the SSIS core ETL functions areperformed within ‘Data Flow Tasks’. Data flows in SSIS are built usingcomponents that define the sources that data comes from, thedestinations it gets loaded to, and the transformations applied to dataduring the transfer.

Referring to FIG. 1A, an example of a complete data flow 100_A describesthe flow of data by specifying one or more source points 102 that startthe flow, operations 104_ downstream of the source points that operateon the data or direct the flow (e.g., merging or splitting the flow),and one or more destination points 106_ that ‘sink’ the data at the endsof the flow. Portions of the data flow (e.g., incomplete data flows, orFlowlets) can be formed from data flow scenarios where only partialportions of the flow are known, such as in the case of a reusable logicportion for example. Referring to FIG. 1B, such reusable logic portions100_B can have one or more source 102 or destination 106 points that areunknown or are unavailable, can have one or more operations within theflow that are unknown 104_3, or any combination of these conditions(indicated by empty dashed-line blocks in FIG. 1B). Traditionally, suchincomplete data flows cannot be executed on their own without accountingfor the missing or unknown source points, destination points, and/oroperations. As described in detail below, the invention provides specialsource and destination flowlet components to enable stand-aloneexecution of such incomplete data flows.

FIG. 2 illustrates the use of special source and destination points,called flowlet source and destination components herein, according toone aspect of the invention. In the resulting modularized data flow,FIG. 2 illustrates an example of 2 flowlets (200_1 and 200_2) formedfrom the incomplete data flow in FIG. 1B using flowlet source component202_1 in place of missing or unknown flat file source 102, and usingflowlet destination components 206_2 in place of missing or unknown OLEDB destination 106_1. Unknown derived column operation 104_3 of FIG. 1Bis replaced by a flowlet destination 206_1 and source component 202_2pair.

Systems and Methods of Modularizing Data Flows

FIG. 3 illustrates the execution flow of an SSIS package 300 withoutflowlets. The execution follows the list of executable elements 302_ andprecedence constraints 304_ defined in the package 300. The executablescan also be grouped in containers 306_ (e.g., sequences or loops) andthey all have access to connection 308 managers (global entities) andvariables 310, which can be global or scoped to appropriate containers.

FIG. 4A illustrates an exemplary execution flow of a new SSIS package400_A specialized to hold flowlet definitions according to one aspect ofthe present invention. The flowlet definitions 402_ can be held in aspecialized SSIS package 400_A. The definitions itself are just asregular data flows except that at least one of its parts starts from aFlowlet Source component (202_ of FIG. 2) or at least one of its pathsterminates on a Flowlet Destination (206_ of FIG. 2). The flowletdefinition data flow 402_ contains at least one Flowlet Source orFlowlet Destination.

FIG. 4B illustrates an exemplary block diagram of a system formodularizing data flows according to one aspect of the presentinvention. In various non-limiting embodiments the system 400_B caninclude: a source flowlet component 412 configured to provide afunctional data source in the data flow logic portion; a destinationflowlet component 414 configured to provide a functional datadestination in the data flow logic portion; a flowlet referencecomponent 416 configured to link the data flow logic portion to one ormore external data flows (not shown); a flowlet metadata mappingcomponent 418 configured to map one or more of the inputs or outputsfrom the one or more external data flows by mapping source 412 anddestination 414 flowlet component inputs or outputs to the flowletreference component. In addition, the system can include a flowletdefinition designer component 420 configured to enable at least one ofthe creation, editing, use, browsing, and a package component 400_Aconfigured to hold a modularized data flow logic portion for at leastone of modularized data flow development or deployment. The system canoptionally contain other components as described more fully below. FIG.4B shows one such optional component (e.g., the debugging component 422shown in dashed lines).

FIG. 4C illustrates an exemplary block diagram of a process formodularizing a data flow logic portion by creating a flowlet definition424 according to one aspect of the present invention. The process caninclude: providing a source flowlet component 426 configured as afunctional data source in the data flow logic portion; providing adestination flowlet component 428 configured as a functional datadestination in the data flow logic portion; providing a flowletreference component 430 configured to link the flowlet definition to oneor more external data flows (not shown); and providing a flowletmetadata mapping 432 component configured to isolate boundaries of theflowlet definition logic from the one or more external data flows bymapping source 426 and destination flowlet component 428 inputs oroutputs to the flowlet reference component.

FIG. 4D illustrates an exemplary block diagram of a process forstand-alone execution of incomplete data flows according to one aspectof the present invention. The process can include: providing a sourceflowlet component 434 operably configured to be a functional data sourcein an incomplete data flow (not shown); providing a destination flowletcomponent 436 operably configured to be a functional data destination inan incomplete data flow; connecting one or more of flowlet source anddestination components to the incomplete data flow 437; providing aflowlet reference component 440 configured to link a flowlet definitionto one or more external data flows; providing a flowlet metadata mappingcomponent 442 configured to map one or more of the inputs or outputsfrom one or more external data flows by mapping source and destinationflowlet component inputs or outputs to the flowlet reference component;and providing a package component 438 operably configured to enableexecution of the incomplete data flow.

Flowlet Definition Designer

According to one aspect of the invention, an improved data flow designeris provided to enable editing of flowlet definitions similar to thecurrent SSIS data flow designer. However, additional capabilities areprovided to design or create new flowlet definitions for all data flowconstructs (e.g., components). In addition to providing access to thenew Flowlet Source and Destination components, the data flow designerprovides access to connection managers & variables to provide similarfull-fidelity experience as the existing SSIS data flow designer.Moreover, the provided designer enables editing capabilities (e.g.,modify) of existing flowlet definitions to change its behavior.

According to a further aspect of the invention, the new data flowdesigner provides the ability to execute flowlet definitions inseparation (e.g., stand-alone execution) and show the executionprogress.

Data Flow Components of Flowlet Definition Modules

According to one aspect of the invention, the use of such flowlet sourceand destination components as functional placeholders for unavailablesource and destination points advantageously allows the flowlets to beexecuted and tested as stand-alone data flows. Furthermore, the flowletsource and destination components can serve as connection points, whichprovide the basis for allowing the stand-alone execution of the flowletto be incorporated into other data flows. According to a further aspectof the invention, such stand-alone data flows can be connected together,as-is, by their respective flowlet source and destination componentswith other data flows.

Flowlet Source Component

According to one aspect of the invention, a Flowlet Source component isprovided which has a single output. Output columns on the flowlet sourceoutput will define metadata of the columns entering the flowlet scopefrom the outer data flow. The flowlet source can produce sample data forthe columns defined on it. The flowlet source is able to generate datafor all available data types.

As described above, a flowlet source component feeds the data flow withdata to start the execution at points of origin. According to a furtheraspect of the invention, developers of data flows can customize how datais generated by specifying different parameters. For example, parameterssuch as the number of rows (e.g., random or fixed) and nature of thedata (e.g., random, fixed, or based on a range, sequence or trend thatcan approximate real or hypothetical data) can be specified.

Flowlet Destination Component

According to a further aspect of the invention, a flowlet destinationcomponent is provided which has a single input. The flowlet destinationoutput will have metadata, of the columns generated inside the scope ofa flowlet, and represented using the external metadata columns. Theseexternal metadata columns define a contract between a flowlet and anouter data flow when flowlet output columns are considered. Thesecolumns further provide for the case when this contract is definedbefore the inner flowlet logic is built. Furthermore, at execution time(flowlet definition execution), the flowlet destination will only absorbdata generated upstream of it, according to one aspect of the invention.

Additionally, as described above, flowlet destination component ‘sinks’data at the end points of the flow. According to a further aspect of theinvention, developers of data flows can customize how the data getsconsumed by specifying different parameters. For example, parameterssuch as drop data (e.g., the data is dropped from the data flow), rowcount (e.g., the number of data rows is counted and recorded), anddumped out (e.g., the data is written out to a file in a minimalfashion) can be specified.

Flowlet Reference Component

According to a further aspect of the invention, a Flowlet ReferenceComponent is provided which is itself a link between the flowletdefinition and a data flow it is used in. The flowlet referencecomponent has the same number of inputs as the referenced flowletdefinition has flowlet sources and it has the same number of outputs asthe flowlet definition has flowlet destinations. Inputs on the flowletreference component are mapped one to one with the flowlet sourcesinside the flowlet definition. The same is true for the flowletreference outputs and flowlet destinations.

According to a further aspect of the invention, the flowlet referencecomponent inputs have external metadata columns with the same metadataas output columns of a linked flowlet source from the flowletdefinition. These external metadata columns can be used to map upstreamoutput columns in the hosting data flow. Additionally, the externalmetadata columns of flowlet destinations can show up as output columnsin appropriate outputs on flowlet reference components. Those columnscan then be automatically made available for downstream mappings as ifthey were created by a regular component.

According to a further aspect of the invention, the instances offlowlets can have the notions of synchronous/asynchronous outputssimilar to the real components.

According to a further aspect of the invention, the runtime connectioncollection on the reference component can be used to publish all theconnections used internally in the flowlet definition to allow mappingof the connection managers defined in the external data flow to thosedefined inside the flowlet definition.

According to a further aspect of the invention, the flowlet referencecomponent does not have any execution time behavior (e.g., it is ignoredwhen execution paths are built).

According to a further aspect of the invention, the flowlet referencecomponent can implement one or more additional interfaces to exposeflowlet specific properties and behavior.

Flowlet Reference Interface

In addition to the regular component interfaces that the flowletreference component implements, it can also expose an additional one ormore interfaces specific to its behavior. For example, such anadditional interface can provide services to load and cache flowletinternals, refresh loaded flowlet internals, persist flowlet internals,and extract selected components from the main flow for reasons such asturning them into flowlet definitions.

Flowlet Metadata Mapping Component

According to a further aspect of the invention, a Flowlet MetadataMapping Component is provided to isolate boundaries of the flowlet logicfrom the outer data flow. Accordingly, the Flowlet Metadata MappingComponent can provide a necessary indirection to protect flowletinternals from the upstream changes in the main data flow and to protectthe downstream components of the main data flow from changes in flowletinternals.

According to a further aspect of the invention, the same instances ofinputs and outputs exposed on the mapping components can be madeavailable as inputs and outputs on associated flowlet referencecomponents. Accordingly, these components have one input and one output.The input contains both input and external metadata columns. The outputcontains output columns. Furthermore, mappings can also be definedbetween external metadata columns and output columns. As a result, thesecomponents are what flowlet sources and flowlet destinations can becomewhen the content of a flowlet definition is loaded as a sub-graph undera flowlet reference component.

According to a further aspect of the invention, the Flowlet MetadataMapping Component does not have execution behavior (e.g., it is ignoredwhile building the execution paths). Moreover, the metadata mappingindirection that this component defines can be resolved at the time theexecution paths are generated. The inner and outer column mappings canalso be reconfigured at that time.

According to a further aspect of the invention, the data flow layout forboth flowlet and regular data flow modules can include internalimplementations of the components: Flowlet Source (202_ of FIG. 2),Flowlet Destination (206_ of FIG. 2), Flowlet Reference (605 of FIGS. 6Cand 6D) and Flowlet Metadata Mapping component (622 and 628 of FIG. 6D).Furthermore, Flowlet Metadata Mapping component 622 can be implementedeither as a separate entity, or it can be built by transforming theFlowlet Source (e.g., adding an input to it) or Flowlet Destination(e.g., adding an output to it). Additionally, the Flowlet Referencecomponent 605 can have a placeholder for data flow sub-graphs withcomponents and paths loaded from associated flowlet definitions.

Modularizing Data Flows by Extraction of Portions of an Existing DataFlow

According to one aspect of the invention, a method of creating amodularized data flow by extracting portions of an existing SSIS dataflow into a flowlet definition module is provided. To extract a portionof an existing SSIS data flow, the process begins by selecting thecomponents to extract at 502 in the provided data flow designer 500_A asillustrated in FIG. 5A and issuing an extract flowlet command 504.

FIG. 5B illustrates an exemplary non-limiting block diagram 500_B of aprocess to create the modularized data flow by extracting portions of anexisting SSIS data flow into a flowlet definition module. At 506, allselected components with their metadata can be copied to a newlygenerated flowlet definition. At 508, all disconnected inputs can beconnected to newly generated flowlet sources. All disconnected outputscan be connected to newly generated flowlet destinations at 510. Ifnecessary, at 512, the selected sub-trees are walked downstream startingfrom disconnected inputs to identify referenced input columns, which canthen be stored in output column collections of appropriate flowletsources. Likewise at 514, if necessary, the sub-trees can be walkedupstream from disconnected outputs to identify outputs columns generatedin the sub-graph, which can then be copied to external columncollections of flowlet destinations. At 516, the selected data flowcomponents are replaced with a single flowlet reference component. Allflowlet sources can then be represented as separate inputs on theflowlet reference and all flowlet destinations can then be turned intooutputs at 518. Additionally, at 520 their external columns can beautomatically mapped with real input/output columns in hosting flows.Advantageously, the configuration of selected sub-flows canautomatically determine whether flowlets are synchronous orasynchronous. At 522, the connection managers and SSIS variablesreferenced in the selected components can then be enumerated on theflowlet reference component, which can serve as placeholders where realentities in the original data flow can be mapped to the correspondingelements inside the flowlet definitions.

Although creation of modular data flows from existing data flows usingthe method above represents one aspect of the invention, it should beapparent that the data flow designer provided by the invention enablesthe creation of such modularized data flows from scratch as indicatedabove. Furthermore, one skilled in the art would recognize other methodsof creating modular data flows according to the framework and conceptsdisclosed herein. As such, the invention should not be limited to thedisclosed embodiments.

To illustrate an example of the method described above, FIG. 6Aillustrates an exemplary simple data flow and the layout of columns thatthe data flow processes. The data flow 600_A consists of the Sourcecomponent 602_A (which produces 4 columns; A (610), B (612), C (614),and D (616)), Transform 1 (604) (which references column A and producescolumn E (618)), Transform 2 (606) (which references B and produces F(620)) and the Destination component 608_A (which consumes all thecolumns generated upstream; e.g., A, B, C, D, E and F). FIG. 6Billustrates an exemplary flowlet definition diagram created byextracting components Transform 1 (604) and Transform 2 (606) of FIG.6A.

In FIG. 6B, the Flowlet Source 602_B produces only the columns extractedcomponents (Transform 1 (604) and Transform 2(606)) consumed. Similarly,the Flowlet Destination 608_B only consumes columns E and F (618 and620) (e.g., the ones generated by the extracted components). As aresult, the Flowlet Destination 608_B has external metadata columns thatmatch metadata of E and F columns (618 and 620), and to which upstreamcolumns can be mapped. For the case in FIG. 6B, Transform 1 andTransform 2 are shown as synchronous components. However for theasynchronous case, only the “pass through” columns (C/D (614/616) onFIG. 6A and A/B (610/612) on FIG. 6B) would be terminated forasynchronous components. FIG. 6C illustrates an exemplary FlowletReference component 605 formed by the extracted components of FIG. 6Aaccording to the process of FIG. 5B. For example, FIG. 6C shows howextracted components (Transform 1 (604) and Transform 2 (606) of FIGS.6A and 6B) are replaced by the Flowlet Reference component (605 of FIG.6C). FIG. 6C shows that Flowlet Reference can behave as an aggregator ofextracted components as far as the usage of column metadata isconsidered.

Caching of Flowlet Definitions in Hosting Data Flows

According to one aspect of the invention, before a data flow thatreferences flowlets can be executed, internals of referenced flowletshave to be cached somewhere in the main flow. Accordingly, the internalcomponents and paths can be stored in separate flowlet containers thatcan be made accessible only through flowlet references 605. The cachedcomponents can be further used to expand the execution paths so they canrepresent the entire data flow the same way as if the flowlet logic isincorporated inline in the main data flow. The SSIS data flow executionengine thus behaves the same way as if it deals with a single monolithicdata flow.

FIG. 6D illustrates how internal flowlet logic can be cached in the caseof an exemplary Flowlet Reference component formed by extractedcomponents as in the case of the example in FIGS. 6A-6C. All the cachedcomponents can be stored in a private container 626 assigned to theflowlet reference. Both the Flowlet Source 602_B and the FlowletDestination 608_B can be replaced by the Flowlet Metadata MappingComponents (622 and 628), and the rest of the components (604 and 606)can be copied and appropriately connected. The Flowlet Source can beturned into the mapping component by copying the output columns andreplicating them into external metadata column collection on the input.The links between external and output columns can be preserved thereby.The input column collection can be made available for referencingupstream columns and mapping them to available external metadatacolumns. The Flowlet Destination can be turned into the mappingcomponent by copying the entire input columns and external metadatacolumn collections and then replicating the external metadata columnsinto the output column collection. The mapping between external metadataand output columns can be preserved the in the same manner as for thecase of the mapping component representing a Flowlet Source.

The states of the other (regular) components (604 and 606) copied fromthe flowlet definition can be copied from the flowlet definition withthe exception of object IDs that are to be regenerated. The IDreferences, inside the scope of cached flowlet objects, are updated aswell to use the newly generated IDs.

According to a further aspect of the invention, caching internal flowletlogic by the hosting data flows can optionally be cached only once whilepersisting the loaded flowlet internals in the hosting package, cachedinitially but refreshed when the original gets changed, or not cachedwith flowlet internals always loaded just before execution.

Execution of Data Flows Using Flowlets

According to one aspect of the invention, a flowlet that is referencedin a hosting data flow behaves as a single component to manipulate thedata according to the operations or steps defined by its inner logic.Advantageously, this does not require a separate data flow executionengine in SSIS that is responsible for flowlets. As a result, theinvention provides the mechanism for flowlets to be incorporated to themain data flow in order to be executed. Thus the execution paths caninclude the inner flowlet components. Thus, the final update of objectIDs that are used to map columns with their upstream origins can bebased on the information contained in the metadata mapping componentsthat separate inner and outer components.

As described above, execution paths are entities that define flowing ofdata in SSIS data flows. Execution paths start at outputs that canproduce data (e.g., sources) and end at inputs that consume the data(e.g., destinations). They also include all synchronous input-outputpairs that only pass the data through.

FIG. 7A illustrates an exemplary SSIS Data Flow graph 700_A and theexecution paths it produces. According to one aspect of the invention,sources 702_ start the execution paths, asynchronous components (704,708, 712) break incoming paths and start new for each asynchronousoutput, synchronous transforms (706, 710) are inside the path, anddestinations 714_ terminate the paths. Additionally, the execution pathsare illustrated by lines defining buffers (shown as bold lines in FIGS.7A-7D). Accordingly, sources generate new buffers, synchronouscomponents process those buffers and pass them thru, asynchronouscomponents (in the general case) collect all the incoming buffers andthen generate new ones and destinations only consume incoming buffers.

According to one aspect of the invention, the introduction of flowletsdoes not change the process of data flow logic. Rather all necessaryinformation about the flowlet to be extracted (716) can be collectedbefore beginning the process of building of execution paths, to be ableto integrate inner flowlet logic into the execution paths the same wayas if that logic is contained in the main data flow.

FIG. 7B illustrates how exemplary execution paths are built for the SSISData Flow graph 700_A of FIG. 7A after the desired flowlet logic (716)is extracted and the flowlet definition 720 is created.

FIGS. 7C and 7D illustrates an example of how columns (and buffers) canbe generated, referenced, and mapped when switching to and from theflowlet boundaries of FIGS. 7A and 7B. According to one aspect of theinvention, column Lineage IDs can be used to map SSIS data flow columnsreferenced (e.g., input columns) in given components with their origins(e.g., output columns) generated upstream. These IDs can be assigned tothe output columns when they are generated, and, accordingly, the IDsshould be unique so they can be used as column identifiers.

After loading flowlet logic into the context of a hosting data flow, thesystem can translate column lineage IDs (and all other object IDs) intoan empty space (e.g., not previously used IDs) so they do not conflictwith already used IDs. As a result, the inner IDs on flowlet metadatamapping components are updated so that the inner/outer column mappingsare in sync with these changes.

As described above, flowlet mapping components can be enumerated and thetable of column lineage ID aliases can be built while building theexecution trees. After finishing the generation of execution trees, theSSIS data flow execution scheduler can enumerate all the columns andassign lineage IDs to column positions in assigned buffer definitionsfor each component. The previously built table of lineage ID aliases canbe used to find appropriate upstream columns for the case where mappedcolumn lineages pass at least one flowlet metadata mapping isolationpoint.

Debugging and Support of Data Flows Using Flowlets

According to a further aspect of the invention, the debuggingexperience, when data flows containing flowlets are executed, is similarto the experience with debugging regular data flows. Progress can bereported and data viewers can be attached on visible paths. According toa further aspect of the invention, flowlet reference components can becolored to represent execution state of inner components itencapsulates. For example, no color change (e.g., white) can indicatethat none of the inner components have started execution, yellow canindicate that at least one component started the execution and there isat least one component which have not finished the execution, green canindicate that all inner components have finished the execution, and redcan indicate that there is at least one inner component which failed inexecution. The selections of such debugging indications are described toillustrate exemplary choices of debugging options available and are notcrucial to the functionality of the invention.

According to a further aspect of the invention, the data flow developercan be presented with debugging information even inside the flowlets.Such information can be provided by passing the progress information toopened flowlet designers and implementing the same debugging visualfeatures in the provided designer as for a regular data flow. Flowletreferences behave as single components in main data flows during theexecution in the designer. The progress events and data viewers canoffer an external view to the flowlets referenced in the main data flow.The flowlet definition designer can be opened from the place it isreferenced in a main flow and internal progress messages can bechanneled to it and displayed at the granularity of a single componentcontained by the flowlet. According to a further aspect of theinvention, it possible to define data viewers inside flowlet definitiondiagrams. As a result, when the execution flow gets inside the flowletinternals, a flowlet can start coloring and show progress and dataviewers for respective components presented in the flowlet definitiondesigner, for example, when opened in a separate window.

According to a further aspect of the invention, error messages, logginginformation, and other events generated by the inner flowlet componentscan also be routed to the appropriate flowlet reference component andthen filtered or aggregated as necessary to further enrich thedevelopment and debugging experience. For example, components defined inflowlets can be loaded into a main data flows for the execution and assuch can pass associated logging information to the log providersdefined in the main data flow. According to a further aspect of theinvention log information emitted by components inside of referencedflowlets can be extended to identify both the flowlet referencecomponent and its inner component.

According to a further aspect of the invention, standard data flowevents can be raised by data flow components such as, information,warning, error, progress, custom event, and query cancel Information,warning and error events can be used for diagnostics of validation,execution and other actions. The progress event can be used to reportthe progress of execution stages (e.g., number of components in a dataflow that are validated, prepared for execution, etc.). The custom eventcan be used to report all other events not covered by the previous four,but most importantly it reports progress of data as it moves in buffers.Query cancel can be used to poll the client whether the execution shouldbe canceled. These events can be used by the data flow developer, forexample to display messages associated with the components that emittedthem, to display progress in a progress view, to show number of rowsflowing through paths, and to color components as they go throughexecution phases or encounter errors as described above.

According to a further aspect of the invention, for some flowletreference components introduced in data flows, some of these events canbe enabled to carry additional information. For example, such additionalinformation may identify a flowlet reference together with its innercomponent that generated an event. Additionally, the capability todefine the logic to filter or aggregate some of the events raised by theinner components of flowlet definitions can be provided to furtherenhance the data flow development experience using flowlets.

Deployment of Data Flow Packages Using Flowlets

According to a further aspect of the invention, deployment utilities canbe provided to cover packages that reference flowlets and to be able todeploy flowlets on their own. Some exemplary deployments scenarios mayinclude deploying flowlet definitions to a common location so they canbe referenced from multiple places, deploying packages that referenceflowlets without used flowlets (e.g., in the case where flowlet logic isalready cached in main packages), deploying packages together with usedflowlets, and deploying flowlet definitions only. For example, if theproject to be deployed contains only flowlet definitions it may bepossible to deploy all those flowlets to a single location. The flowletscan be deployed the same way that packages are deployed currently withthe exception of a different destination table (e.g., a msdb database)if flowlets are moved to SQL Server.

Furthermore, if a deployed project contains both package and flowletitems in their folders, all of them can be deployed. A separatedestination pages on a deployment wizard can be provided to specifyflowlet and package destination server or folder. As a furtherillustration, the deployment logic can follow the steps of going throughthe entire package to look for the flowlet reference components, get tothe connection managers (e.g., File and SQL Server connections) used todefine locations of flowlet definitions from the list of flowletreference components, build a list of unique flowlet definitions fromthe connection managers pointing to files, find those among the flowletdefinitions identified in the last step that are already cached to themain flow in all instances where they are referenced, offer this list inthe wizard grid so users can choose which ones to deploy with associatedpackages, show the rest of the list as flowlets that will also bedeployed, and the go through the list of flowlet definitions to bedeployed and update connections managers pointing to them in the mainpackage. Note that flowlets published to SQL Server do not need to bemoved as their references will not be broken.

Storage and Management of Flowlet Definitions

According to a further aspect of the invention, flowlet definitions canbe saved to three types of storage (e.g., files, dedicated table in theSQL Server system database, and SSIS Storage) just as for regular SSISpackages. Moreover, flowlet browsing capability can be provided in theprovided SSIS designer as well as to other relevant clients.

Additionally, the invention provides the possibility to choose amongmultiple ways to protect flowlet definitions. For example, a data flowdeveloper can choose to not save sensitive data, encrypt sensitive datawith user key or password, encrypt all data with user key or password,or rely on server storage and roles for access control. Accordingly, thedefault flowlet definition protection can be to encrypt sensitive datawith user key. As a result, only the same user would be allowed to loadthe package in whole and a different user will see blanks replacing thesensitive information and fail to execute the package. Similarly, theencrypt all data with user key provides that a different user will notbe able to open the package at all. Such options can rely on thefunction EncryptStringWithUserKey from MICROSOFT Data Protection API(DPAPI) to do the encryption or other equivalent encryption APIs orfunctionality. As an example, if the protection level is set to encryptsensitive data with password, a screen asking for password can beprovided when a user opens a package. Failing to provide a correctpassword causes the package to open without sensitive data loaded andthe package will not be executed. Similarly, if the protection level isset to encrypt all data with password, a user is unable to open orexecute the data if correct password is not entered appropriately.Lastly, a protection level set to rely on server storage and roles foraccess control protects a package with database-level roles only when apackage is saved to SQL database.

Exemplary Networked and Distributed Environments

One of ordinary skill in the art can appreciate that the invention canbe implemented in connection with any computer or other client or serverdevice, which can be deployed as part of a computer network, or in adistributed computing environment, connected to any kind of data store.In this regard, the present invention pertains to any computer system orenvironment having any number of memory or storage units, and any numberof applications and processes occurring across any number of storageunits or volumes, which may be used in connection with modularizing dataflows or providing stand-alone execution of incomplete data flows inaccordance with the present invention. The present invention may applyto an environment with server computers and client computers deployed ina network environment or a distributed computing environment, havingremote or local storage. The present invention may also be applied tostandalone computing devices, having programming language functionality,interpretation and execution capabilities for generating, receiving andtransmitting information in connection with remote or local services andprocesses. Modularizing data flows or providing stand-alone execution ofincomplete data flows and the related techniques in accordance with thepresent invention can be applied with great efficacy in thoseenvironments.

Distributed computing provides sharing of computer resources andservices by exchange between computing devices and systems. Theseresources and services include the exchange of information, cachestorage and disk storage for objects, such as files. Distributedcomputing takes advantage of network connectivity, allowing clients toleverage their collective power to benefit the entire enterprise. Inthis regard, a variety of devices may have applications, objects orresources that may implicate the systems and methods for modularizingdata flows or providing stand-alone execution of incomplete data flowsaccording to the disclosed invention.

FIG. 8 provides a schematic diagram of an exemplary networked ordistributed computing environment. The distributed computing environmentcomprises computing objects 810 a, 810 b, etc. and computing objects ordevices 820 a, 820 b, 820 c, 820 d, 820 e, etc. These objects maycomprise programs, methods, data stores, programmable logic, etc. Theobjects may comprise portions of the same or different devices such asPDAs, audio/video devices, MP3 players, personal computers, etc. Eachobject can communicate with another object by way of the communicationsnetwork 840. This network may itself comprise other computing objectsand computing devices that provide services to the system of FIG. 8, andmay itself represent multiple interconnected networks. In accordancewith an aspect of the invention, each object 810 a, 810 b, etc. or 820a, 820 b, 820 c, 820 d, 820 e, etc. may contain an application thatmight make use of an API, or other object, software, firmware and/orhardware, suitable for use with the systems and methods for modularizingdata flows or providing stand-alone execution of incomplete data flowsin accordance with the invention.

It can also be appreciated that an object, such as 820 c, may be hostedon another computing device 810 a, 810 b, etc. or 820 a, 820 b, 820 c,820 d, 820 e, etc. Thus, although the physical environment depicted mayshow the connected devices as computers, such illustration is merelyexemplary and the physical environment may alternatively be depicted ordescribed comprising various digital devices such as PDAs, televisions,MP3 players, etc., any of which may employ a variety of wired andwireless services, software objects such as interfaces, COM objects, andthe like.

There are a variety of systems, components, and network configurationsthat support distributed computing environments. For example, computingsystems may be connected together by wired or wireless systems, by localnetworks or widely distributed networks. Currently, many of the networksare coupled to the Internet, which provides an infrastructure for widelydistributed computing and encompasses many different networks. Any ofthe infrastructures may be used for exemplary communications madeincident to modularizing data flows or providing stand-alone executionof incomplete data flows according to the present invention.

In home networking environments, there are at least four disparatenetwork transport media that may each support a unique protocol, such asPower line, data (both wireless and wired), voice (e.g., telephone) andentertainment media. Most home control devices such as light switchesand appliances may use power lines for connectivity. Data Services mayenter the home as broadband (e.g., either DSL or Cable modem) and areaccessible within the home using either wireless (e.g., HomeRF or802.11B) or wired (e.g., Home PNA, Cat 5, Ethernet, even power line)connectivity. Voice traffic may enter the home either as wired (e.g.,Cat 3) or wireless (e.g., cell phones) and may be distributed within thehome using Cat 3 wiring. Entertainment media, or other graphical data,may enter the home either through satellite or cable and is typicallydistributed in the home using coaxial cable. IEEE 1394 and DVI are alsodigital interconnects for clusters of media devices. All of thesenetwork environments and others that may emerge, or already haveemerged, as protocol standards may be interconnected to form a network,such as an intranet, that may be connected to the outside world by wayof a wide area network, such as the Internet. In short, a variety ofdisparate sources exist for the storage and transmission of data, andconsequently, any of the computing devices of the present invention mayshare and communicate data in any existing manner, and no one waydescribed in the embodiments herein is intended to be limiting.

The Internet commonly refers to the collection of networks and gatewaysthat utilize the Transmission Control Protocol/Internet Protocol(TCP/IP) suite of protocols, which are well-known in the art of computernetworking. The Internet can be described as a system of geographicallydistributed remote computer networks interconnected by computersexecuting networking protocols that allow users to interact and shareinformation over network(s). Because of such wide-spread informationsharing, remote networks such as the Internet have thus far generallyevolved into an open system with which developers can design softwareapplications for performing specialized operations or services,essentially without restriction.

Thus, the network infrastructure enables a host of network topologiessuch as client/server, peer-to-peer, or hybrid architectures. The“client” is a member of a class or group that uses the services ofanother class or group to which it is not related. Thus, in computing, aclient is a process, e.g., roughly a set of instructions or tasks, thatrequests a service provided by another program. The client processutilizes the requested service without having to “know” any workingdetails about the other program or the service itself. In aclient/server architecture, particularly a networked system, a client isusually a computer that accesses shared network resources provided byanother computer, e.g., a server. In the illustration of FIG. 8, as anexample, computers 820 a, 820 b, 820 c, 820 d, 820 e, etc. can bethought of as clients and computers 810 a, 810 b, etc. can be thought ofas servers where servers 810 a, 810 b, etc. maintain the data that isthen replicated to client computers 820 a, 820 b, 820 c, 820 d, 820 e,etc., although any computer can be considered a client, a server, orboth, depending on the circumstances. Any of these computing devices maybe processing data or requesting services or tasks that may implicatethe systems and methods for modularizing data flows or providingstand-alone execution of incomplete data flows in accordance with theinvention.

A server is typically a remote computer system accessible over a remoteor local network, such as the Internet or wireless networkinfrastructures. The client process may be active in a first computersystem, and the server process may be active in a second computersystem, communicating with one another over a communications medium,thus providing distributed functionality and allowing multiple clientsto take advantage of the information-gathering capabilities of theserver. Any software objects utilized pursuant to the techniques formodularizing data flows or providing stand-alone execution of incompletedata flows of the invention may be distributed across multiple computingdevices or objects.

Client(s) and server(s) communicate with one another utilizing thefunctionality provided by protocol layer(s). For example, HyperTextTransfer Protocol (HTTP) is a common protocol that is used inconjunction with the World Wide Web (WWW), or “the Web.” Typically, acomputer network address such as an Internet Protocol (IP) address orother reference such as a Universal Resource Locator (URL) can be usedto identify the server or client computers to each other. The networkaddress can be referred to as a URL address. Communication can beprovided over a communications medium, e.g., client(s) and server(s) maybe coupled to one another via TCP/IP connection(s) for high-capacitycommunication.

Thus, FIG. 8 illustrates an exemplary networked or distributedenvironment, with server(s) in communication with client computer (s)via a network/bus, in which the present invention may be employed. Inmore detail, a number of servers 810 a, 810 b, etc. are interconnectedvia a communications network/bus 840, which may be a LAN, WAN, intranet,GSM network, the Internet, etc., with a number of client or remotecomputing devices 820 a, 820 b, 820 c, 820 d, 820 e, etc., such as aportable computer, handheld computer, thin client, networked appliance,or other device, such as a VCR, TV, oven, light, heater and the like inaccordance with the present invention. It is thus contemplated that thepresent invention may apply to any computing device in connection withwhich it is desirable to modularize data flows or provide stand-aloneexecution of incomplete data flows.

In a network environment in which the communications network/bus 840 isthe Internet, for example, the servers 810 a, 810 b, etc. can be Webservers with which the clients 820 a, 820 b, 820 c, 820 d, 820 e, etc.communicate via any of a number of known protocols such as HTTP. Servers810 a, 810 b, etc. may also serve as clients 820 a, 820 b, 820 c, 820 d,820 e, etc., as may be characteristic of a distributed computingenvironment.

As mentioned, communications may be wired or wireless, or a combination,where appropriate. Client devices 820 a, 820 b, 820 c, 820 d, 820 e,etc. may or may not communicate via communications network/bus 14, andmay have independent communications associated therewith. For example,in the case of a TV or VCR, there may or may not be a networked aspectto the control thereof. Each client computer 820 a, 820 b, 820 c, 820 d,820 e, etc. and server computer 810 a, 810 b, etc. may be equipped withvarious application program modules or objects 135 a, 135 b, 135 c, etc.and with connections or access to various types of storage elements orobjects, across which files or data streams may be stored or to whichportion(s) of files or data streams may be downloaded, transmitted ormigrated. Any one or more of computers 810 a, 810 b, 820 a, 820 b, 820c, 820 d, 820 e, etc. may be responsible for the maintenance andupdating of a database 830 or other storage element, such as a databaseor memory 830 for storing data processed or saved according to theinvention. Thus, the present invention can be utilized in a computernetwork environment having client computers 820 a, 820 b, 820 c, 820 d,820 e, etc. that can access and interact with a computer network/bus 840and server computers 810 a, 810 b, etc. that may interact with clientcomputers 820 a, 820 b, 820 c, 820 d, 820 e, etc. and other likedevices, and databases 830.

Exemplary Computing Device

As mentioned, the invention applies to any device wherein it may bedesirable to modularize data flows or provide stand-alone execution ofincomplete data flows. It should be understood, therefore, thathandheld, portable and other computing devices and computing objects ofall kinds are contemplated for use in connection with the presentinvention. Accordingly, the below general purpose remote computerdescribed below in FIG. 9 is but one example, and the present inventionmay be implemented with any client having network/bus interoperabilityand interaction. Thus, the present invention may be implemented in anenvironment of networked hosted services in which very little or minimalclient resources are implicated, e.g., a networked environment in whichthe client device serves merely as an interface to the network/bus, suchas an object placed in an appliance.

Although not required, the invention can partly be implemented via anoperating system, for use by a developer of services for a device orobject, and/or included within application software that operates inconnection with the component(s) of the invention. Software may bedescribed in the general context of computer-executable instructions,such as program modules, being executed by one or more computers, suchas client workstations, servers or other devices. Those skilled in theart will appreciate that the invention may be practiced with othercomputer system configurations and protocols.

FIG. 9 thus illustrates an example of a suitable computing systemenvironment 900 a in which the invention may be implemented, although asmade clear above, the computing system environment 900 a is only oneexample of a suitable computing environment for a media device and isnot intended to suggest any limitation as to the scope of use orfunctionality of the invention. Neither should the computing environment900 a be interpreted as having any dependency or requirement relating toany one or combination of components illustrated in the exemplaryoperating environment 900 a.

With reference to FIG. 9, an exemplary remote device for implementingthe invention includes a general purpose computing device in the form ofa computer 910 a. Components of computer 910 a may include, but are notlimited to, a processing unit 920 a, a system memory 930 a, and a systembus 921 a that couples various system components including the systemmemory to the processing unit 920 a. The system bus 921 a may be any ofseveral types of bus structures including a memory bus or memorycontroller, a peripheral bus, and a local bus using any of a variety ofbus architectures.

Computer 910 a typically includes a variety of computer readable media.Computer readable media can be any available media that can be accessedby computer 910 a. By way of example, and not limitation, computerreadable media may comprise computer storage media and communicationmedia. Computer storage media includes both volatile and nonvolatile,removable and non-removable media implemented in any method ortechnology for storage of information such as computer readableinstructions, data structures, program modules or other data. Computerstorage media includes, but is not limited to, RAM, ROM, EEPROM, flashmemory or other memory technology, CDROM, digital versatile disks (DVD)or other optical disk storage, magnetic cassettes, magnetic tape,magnetic disk storage or other magnetic storage devices, or any othermedium which can be used to store the desired information and which canbe accessed by computer 910 a. Communication media typically embodiescomputer readable instructions, data structures, program modules orother data in a modulated data signal such as a carrier wave or othertransport mechanism and includes any information delivery media.

The system memory 930 a may include computer storage media in the formof volatile and/or nonvolatile memory such as read only memory (ROM)and/or random access memory (RAM). A basic input/output system (BIOS),containing the basic routines that help to transfer information betweenelements within computer 910 a, such as during start-up, may be storedin memory 930 a. Memory 930 a typically also contains data and/orprogram modules that are immediately accessible to and/or presentlybeing operated on by processing unit 920 a. By way of example, and notlimitation, memory 930 a may also include an operating system,application programs, other program modules, and program data.

The computer 910 a may also include other removable/non-removable,volatile/nonvolatile computer storage media. For example, computer 910 acould include a hard disk drive that reads from or writes tonon-removable, nonvolatile magnetic media, a magnetic disk drive thatreads from or writes to a removable, nonvolatile magnetic disk, and/oran optical disk drive that reads from or writes to a removable,nonvolatile optical disk, such as a CD-ROM or other optical media. Otherremovable/non-removable, volatile/nonvolatile computer storage mediathat can be used in the exemplary operating environment include, but arenot limited to, magnetic tape cassettes, flash memory cards, digitalversatile disks, digital video tape, solid state RAM, solid state ROMand the like. A hard disk drive is typically connected to the system bus921 a through a non-removable memory interface such as an interface, anda magnetic disk drive or optical disk drive is typically connected tothe system bus 921 a by a removable memory interface, such as aninterface.

A user may enter commands and information into the computer 910 athrough input devices such as a keyboard and pointing device, commonlyreferred to as a mouse, trackball or touch pad. Other input devices mayinclude a microphone, joystick, game pad, satellite dish, scanner, orthe like. These and other input devices are often connected to theprocessing unit 920 a through user input 940 a and associatedinterface(s) that are coupled to the system bus 921 a, but may beconnected by other interface and bus structures, such as a parallelport, game port or a universal serial bus (USB). A graphics subsystemmay also be connected to the system bus 921 a. A monitor or other typeof display device is also connected to the system bus 921 a via aninterface, such as output interface 950 a, which may in turn communicatewith video memory. In addition to a monitor, computers may also includeother peripheral output devices such as speakers and a printer, whichmay be connected through output interface 950 a.

The computer 910 a may operate in a networked or distributed environmentusing logical connections to one or more other remote computers, such asremote computer 970 a, which may in turn have media capabilitiesdifferent from device 910 a. The remote computer 970 a may be a personalcomputer, a server, a router, a network PC, a peer device or othercommon network node, or any other remote media consumption ortransmission device, and may include any or all of the elementsdescribed above relative to the computer 910 a. The logical connectionsdepicted in FIG. 9 include a network 971 a, such local area network(LAN) or a wide area network (WAN), but may also include othernetworks/buses. Such networking environments are commonplace in homes,offices, enterprise-wide computer networks, intranets and the Internet.

When used in a LAN networking environment, the computer 910 a isconnected to the LAN 971 a through a network interface or adapter. Whenused in a WAN networking environment, the computer 910 a typicallyincludes a communications component, such as a modem, or other means forestablishing communications over the WAN, such as the Internet. Acommunications component, such as a modem, which may be internal orexternal, may be connected to the system bus 921 a via the user inputinterface of input 940 a, or other appropriate mechanism. In a networkedenvironment, program modules depicted relative to the computer 910 a, orportions thereof, may be stored in a remote memory storage device. Itwill be appreciated that the network connections shown and described areexemplary and other means of establishing a communications link betweenthe computers may be used.

Exemplary Distributed Computing Architectures

Various distributed computing frameworks have been and are beingdeveloped in light of the convergence of personal computing and theInternet. Individuals and business users alike are provided with aseamlessly interoperable and Web-enabled interface for applications andcomputing devices, making computing activities increasingly Web browseror network-oriented.

For example, MICROSOFT®'s managed code platform, e.g., .NET, includesservers, building-block services, such as Web-based data storage anddownloadable device software. Generally speaking, the .NET platformprovides (1) the ability to make the entire range of computing deviceswork together and to have user information automatically updated andsynchronized on all of them, (2) increased interactive capability forWeb pages, enabled by greater use of XML rather than HTML, (3) onlineservices that feature customized access and delivery of products andservices to the user from a central starting point for the management ofvarious applications, such as e-mail, for example, or software, such asOffice .NET, (4) centralized data storage, which increases efficiencyand ease of access to information, as well as synchronization ofinformation among users and devices, (5) the ability to integratevarious communications media, such as e-mail, faxes, and telephones, (6)for developers, the ability to create reusable modules, therebyincreasing productivity and reducing the number of programming errorsand (7) many other cross-platform and language integration features aswell.

While some exemplary embodiments herein are described in connection withsoftware, such as an application programming interface (API), residingon a computing device, one or more portions of the invention may also beimplemented via an operating system, or a “middle man” object, a controlobject, hardware, firmware, intermediate language instructions orobjects, etc., such that the methods for modularizing data flows orproviding stand-alone execution of incomplete data flows in accordancewith the invention may be included in, supported in or accessed via allof the languages and services enabled by managed code, such as .NETcode, and in other distributed computing frameworks as well.

There are multiple ways of implementing the present invention, e.g., anappropriate API, tool kit, driver code, operating system, control,standalone or downloadable software object, etc. which enablesapplications and services to use the systems and methods formodularizing data flows or providing stand-alone execution of incompletedata flows of the invention. The invention contemplates the use of theinvention from the standpoint of an API (or other software object), aswell as from a software or hardware object that modularizes data flowsor provides stand-alone execution of incomplete data flows in accordancewith the invention. Thus, various implementations of the inventiondescribed herein may have aspects that are wholly in hardware, partly inhardware and partly in software, as well as in software.

The word “exemplary” is used herein to mean serving as an example,instance, or illustration. For the avoidance of doubt, the subjectmatter disclosed herein is not limited by such examples. In addition,any aspect or design described herein as “exemplary” is not necessarilyto be construed as preferred or advantageous over other aspects ordesigns, nor is it meant to preclude equivalent exemplary structures andtechniques known to those of ordinary skill in the art. Furthermore, tothe extent that the terms “includes,” “has,” “contains,” and othersimilar words are used in either the detailed description or the claims,for the avoidance of doubt, such terms are intended to be inclusive in amanner similar to the term “comprising” as an open transition wordwithout precluding any additional or other elements.

As mentioned above, while exemplary embodiments of the present inventionhave been described in connection with various computing devices andnetwork architectures, the underlying concepts may be applied to anycomputing device or system in which it is desirable to modularize dataflows or provide stand-alone execution of incomplete data flows. Forinstance, some of the components of the invention may be applied to theoperating system of a computing device, provided as a separate object onthe device, as part of another object, as a reusable control, as adownloadable object from a server, as a “middle man” between a device orobject and the network, as a distributed object, as hardware, in memory,a combination of any of the foregoing, etc. While exemplary programminglanguages, names and examples are chosen herein as representative ofvarious choices, these languages, names and examples are not intended tobe limiting. One of ordinary skill in the art will appreciate that thereare numerous ways of providing object code and nomenclature thatachieves the same, similar or equivalent functionality achieved by thevarious embodiments of the invention.

As mentioned, the various techniques described herein may be implementedin connection with hardware or software or, where appropriate, with acombination of both. As used herein, the terms “component,” “system” andthe like are likewise intended to refer to a computer-related entity,either hardware, a combination of hardware and software, software, orsoftware in execution. For example, a component may be, but is notlimited to being, a process running on a processor, a processor, anobject, an executable, a thread of execution, a program, and/or acomputer. By way of illustration, both an application running oncomputer and the computer can be a component. One or more components mayreside within a process and/or thread of execution and a component maybe localized on one computer and/or distributed between two or morecomputers.

Thus, the methods and apparatus of the present invention, or certainaspects or portions thereof, may take the form of program code (e.g.,instructions) embodied in tangible media, such as floppy diskettes,CD-ROMs, hard drives, or any other machine-readable storage medium,wherein, when the program code is loaded into and executed by a machine,such as a computer, the machine becomes an apparatus for practicing theinvention. In the case of program code execution on programmablecomputers, the computing device generally includes a processor, astorage medium readable by the processor (including volatile andnon-volatile memory and/or storage elements), at least one input device,and at least one output device. One or more programs that may implementor utilize the methods or components of the present invention, e.g.,through the use of a data processing API, reusable controls, or thelike, are preferably implemented in a high level procedural or objectoriented programming language to communicate with a computer system.However, the program(s) can be implemented in assembly or machinelanguage, if desired. In any case, the language may be a compiled orinterpreted language, and combined with hardware implementations.

The methods and apparatus of the present invention may also be practicedvia communications embodied in the form of program code that istransmitted over some transmission medium, such as over electricalwiring or cabling, through fiber optics, or via any other form oftransmission, wherein, when the program code is received and loaded intoand executed by a machine, such as an EPROM, a gate array, aprogrammable logic device (PLD), a client computer, etc., the machinebecomes an apparatus for practicing the invention. When implemented on ageneral-purpose processor, the program code combines with the processorto provide a unique apparatus that operates to invoke the functionalityof the present invention. Additionally, any storage techniques used inconnection with the present invention may invariably be a combination ofhardware and software.

Furthermore, the disclosed subject matter may be implemented as asystem, method, apparatus, or article of manufacture using standardprogramming and/or engineering techniques to produce software, firmware,hardware, or any combination thereof to control a computer or processorbased device to implement aspects detailed herein. The term “article ofmanufacture” (or alternatively, “computer program product”) where usedherein is intended to encompass a computer program accessible from anycomputer-readable device, carrier, or media. For example, computerreadable media can include but are not limited to magnetic storagedevices (e.g., hard disk, floppy disk, magnetic strips . . . ), opticaldisks (e.g., compact disk (CD), digital versatile disk (DVD) . . . ),smart cards, and flash memory devices (e.g., card, stick). Additionally,it is known that a carrier wave can be employed to carrycomputer-readable electronic data such as those used in transmitting andreceiving electronic mail or in accessing a network such as the Internetor a local area network (LAN).

The aforementioned systems have been described with respect tointeraction between several components. It can be appreciated that suchsystems and components can include those components or specifiedsub-components, some of the specified components or sub-components,and/or additional components, and according to various permutations andcombinations of the foregoing. Sub-components can also be implemented ascomponents communicatively coupled to other components rather thanincluded within parent components (hierarchical). Additionally, itshould be noted that one or more components may be combined into asingle component providing aggregate functionality or divided intoseveral separate sub-components, and any one or more middle layers, suchas a management layer, may be provided to communicatively couple to suchsub-components in order to provide integrated functionality. Anycomponents described herein may also interact with one or more othercomponents not specifically described herein but generally known bythose of skill in the art.

In view of the exemplary systems described supra, methodologies that maybe implemented in accordance with the disclosed subject matter will bebetter appreciated with reference to the flowcharts of FIGS. 1-7. Whilefor purposes of simplicity of explanation, the methodologies are shownand described as a series of blocks, it is to be understood andappreciated that the claimed subject matter is not limited by the orderof the blocks, as some blocks may occur in different orders and/orconcurrently with other blocks from what is depicted and describedherein. Where non-sequential, or branched, flow is illustrated viaflowchart, it can be appreciated that various other branches, flowpaths, and orders of the blocks, may be implemented which achieve thesame or a similar result. Moreover, not all illustrated blocks may berequired to implement the methodologies described hereinafter.

Furthermore, as will be appreciated various portions of the disclosedsystems above and methods below may include or consist of artificialintelligence or knowledge or rule based components, sub-components,processes, means, methodologies, or mechanisms (e.g., support vectormachines, neural networks, expert systems, Bayesian belief networks,fuzzy logic, data fusion engines, classifiers . . . ). Such components,inter alia, can automate certain mechanisms or processes performedthereby to make portions of the systems and methods more adaptive aswell as efficient and intelligent.

While the present invention has been described in connection with thepreferred embodiments of the various figures, it is to be understoodthat other similar embodiments may be used or modifications andadditions may be made to the described embodiment for performing thesame function of the present invention without deviating therefrom. Forexample, while exemplary network environments of the invention aredescribed in the context of a networked environment, such as a peer topeer networked environment, one skilled in the art will recognize thatthe present invention is not limited thereto, and that the methods, asdescribed in the present application may apply to any computing deviceor environment, such as a gaming console, handheld computer, portablecomputer, etc., whether wired or wireless, and may be applied to anynumber of such computing devices connected via a communications network,and interacting across the network. Furthermore, it should be emphasizedthat a variety of computer platforms, including handheld deviceoperating systems and other application specific operating systems arecontemplated, especially as the number of wireless networked devicescontinues to proliferate.

While exemplary embodiments refer to utilizing the present invention inthe context of particular programming language constructs, the inventionis not so limited, but rather may be implemented in any language toprovide methods for modularizing data flows or providing stand-aloneexecution of incomplete data flows. Still further, the present inventionmay be implemented in or across a plurality of processing chips ordevices, and storage may similarly be effected across a plurality ofdevices. Therefore, the present invention should not be limited to anysingle embodiment, but rather should be construed in breadth and scopein accordance with the appended claims.

1. A computer-implemented method for executing an incomplete data flowcomprising: identifying an incomplete SQL Server Integration Services(SSIS) data flow having at least one missing or unknown source point,destination point or operation which must be accounted for before theincomplete SSIS data flow can be executed; providing one or more dataflowlet components, each of the data flowlet components having anassociated flowlet definition, flowlet internals, and one or more ofinputs and outputs; connecting the data flowlet component to theincomplete SSIS data flow in place of the at least one missing orunknown source point, destination point or operation; providing aflowlet reference component configured to link the one or more flowletdefinitions to one or more external data flows, each one or moreexternal data flow having one or more of inputs and outputs; providing aflowlet metadata mapping component configured to map at least one of theinputs or outputs of the one or more external data flows by mapping atleast one of the inputs or outputs of the one or more of the dataflowlet components to the flowlet reference component; and executing theincomplete SSIS data flow using a package component, wherein theincomplete SSIS data flow that is executed has been modified to nowinclude the data flowlet component in place of the at least one missingor unknown source point, destination point or operation.
 2. The methodof claim 1, wherein providing one or more data flowlet componentsincludes providing a source flowlet component which operates as afunctional data source in the incomplete data flow, the source flowlethaving one or more outputs.
 3. The method of claim 1, wherein providingone or more data flowlet components includes providing a destinationflowlet component which operates as a functional data destination in theincomplete data flow, the destination flowlet having one or more inputs.4. The method of claim 1, wherein providing one or more data flowletcomponents includes providing an operation flowlet component for anunknown operation.
 5. The method of claim 1, wherein providing one ormore data flowlet components includes selecting one or more previouslydefined data flowlet components from a library of reusable data flowletcomponents.
 6. The method of claim 1, further comprising: debugging theincomplete data flow using a debugging component configured to provideat least one of monitoring, indication, logging, and feedbackcapabilities.
 7. The method of claim 1, wherein the flowlet referencecomponent is further configured to provide an interface for providingservices to load and cache flowlet internals, refresh loaded flowletinternals, and persist flowlet internals.
 8. The method of claim 1,further comprising: integrating the incomplete data flow into anexisting data flow layout architecture to preserve one or more ofexisting objects and interfaces.
 9. A computer readable storage mediumcomprising stored computer executable instructions which, when executedby a computing system, implement a method comprising: identifying anincomplete SQL Server Integration Services (SSIS) data flow having atleast one missing or unknown source point, destination point oroperation which must be accounted for before the incomplete SSIS dataflow can be executed; providing one or more data flowlet components,each of the data flowlet components having an associated flowletdefinition, flowlet internals, and one or more of inputs and outputs;connecting the data flowlet component to the incomplete SSIS data flowin place of the at least one missing or unknown source point,destination point or operation; providing a flowlet reference componentconfigured to link the one or more flowlet definitions to one or moreexternal data flows, each one or more external data flow having one ormore of inputs and outputs; providing a flowlet metadata mappingcomponent configured to map at least one of the inputs or outputs of theone or more external data flows by mapping at least one of the inputs oroutputs of the one or more of the data flowlet components to the flowletreference component; and executing the incomplete SSIS data flow using apackage component, wherein the incomplete SSIS data flow that isexecuted has been modified to now include the data flowlet component inplace of the at least one missing or unknown source point, destinationpoint or operation.
 10. A computing device comprising a processor andmemory storing computer-executable instructions which, when executed bythe processor, implement a method comprising: identifying an incompleteSQL Server Integration Services (SSIS) data flow having at least onemissing or unknown source point, destination point or operation whichmust be accounted for before the incomplete SSIS data flow can beexecuted; providing one or more data flowlet components, each of thedata flowlet components having an associated flowlet definition, flowletinternals, and one or more of inputs and outputs; connecting the dataflowlet component to the incomplete SSIS data flow in place of the atleast one missing or unknown source point, destination point oroperation; providing a flowlet reference component configured to linkthe one or more flowlet definitions to one or more external data flows,each one or more external data flow having one or more of inputs andoutputs; providing a flowlet metadata mapping component configured tomap at least one of the inputs or outputs of the one or more externaldata flows by mapping at least one of the inputs or outputs of the oneor more of the data flowlet components to the flowlet referencecomponent; and executing the incomplete SSIS data flow using a packagecomponent, wherein the incomplete SSIS data flow that is executed hasbeen modified to now include the data flowlet component in place of theat least one missing or unknown source point, destination point oroperation.